<?php
/**
 * Created by PhpStorm.
 * User: alei
 * Date: 11/5/2017 AD
 * Time: 16:58
 */
namespace App\Models\Mysql;

//use Illuminate\Database\Eloquent\Model;
use App\Models\Traits\BaseDBMethod;

class pre_ct_task
{
	use BaseDBMethod;
	//数据库连接配置项，见config/database.php
	const DATABASE_CONF = 'lqyw_mysql';
	//pre_ct_ui 首页轮播图所在表
	const TABLE_NAME = 'pre_ct_task';

	//数据库连接配置项，见config/database.php
	//如果需要用ORM，需要设置此项
//	protected $connection = self::DATABASE_CONF;
	//表名，如果需要用ORM，需要设置此项
//	protected $table = self::TABLE_NAME;

	public static function get_task($param)
	{
		return self::getConnObj()->select(
			"SELECT a.taskid,a.difficulty,a.maxsubmitnumber,a.srctid,a.commentendtime,a.submittime,b.`subject`,b.message,b.author,b.dateline,a.polltid,a.status taskStatus FROM pre_ct_task a left join pre_forum_post b ON 
             a.srctid=b.tid  WHERE a.srctid=:srctid limit 1",
			[
				'srctid' => $param['tid'],
			]
		);
	}


    public static function task($param)
    {
        return self::getConnObj()->select(
            "SELECT * FROM pre_ct_task  WHERE taskid=:taskid limit 1",
            [
                'taskid' => $param['taskid'],
            ]
        );
    }

    #
    public static function get_info($param)
    {
        return self::getConnObj()->select(
            "SELECT a.*,b.`subject`,b.message FROM pre_ct_task a left join pre_forum_post b ON
             a.srctid=b.tid  WHERE a.srctid=:srctid limit 1",
            [
                'srctid' => $param,
            ]
        );

    }




    public static function get_count($param)
    {
        return self::getConnObj()->select(
            "SELECT count(*) count FROM  pre_forum_post   WHERE tid=:tid  and pid <> :pid",
            [
                'tid' => $param['tid'],
                'pid' => $param['pid'],
            ]
        );
    }

    public static function get_taskid($param)
    {
        return self::getConnObj()->select(
            "SELECT taskid FROM  pre_ct_task   WHERE srctid=:tid limit 1",
            [
                'tid' => $param['tid'],

            ]
        );
    }

    #统计任务总条数
    public static function task_count($param)
    {
        if($param['status']==3 && ($param['start_time'] || $param['end_time'] || $param['seriesId']>-1 ))
        {
			$postWhere = "";
			if ($param['start_time'])  $postWhere .= " AND commentendtime >= {$param['start_time']} ";
			if ($param['end_time'])    {$end_time=$param['end_time']+86400; $postWhere .= (" AND commentendtime<=  ".$end_time." ");}
			if ($param['seriesId']>-1) $postWhere .= " AND seriesid = {$param['seriesId']} ";

            return self::getConnObj()->select(
                "SELECT count(*) count".
				" FROM pre_ct_task a, pre_forum_post b  ".
				" WHERE a.srctid=b.tid AND a.`status`= :status ".
				$postWhere,
                [
                    'status' => $param['status'],
                ]
            );
        }		
/*
        if($param['status']==3 && $param['start_time'] && $param['end_time'])
        {
            return self::getConnObj()->select(
                "SELECT count(*) count FROM  pre_ct_task   WHERE status=:status and commentendtime BETWEEN {$param['start_time']} and {$param['end_time']}",
                [
                    'status' => $param['status'],
                ]
            );
        }
*/
        else
        {
            return self::getConnObj()->select(
                "SELECT count(*) count FROM  pre_ct_task   WHERE status=:status ",
                [
                    'status' => $param['status'],
                ]
            );
        }
    }
    #任务列表
    public static function task_list($param)
    {
        if($param['status']==3 && ($param['start_time'] || $param['end_time'] || $param['seriesId']>-1 ))
        {
			$postWhere = "";
			if ($param['start_time'])  $postWhere .= " AND commentendtime >= {$param['start_time']} ";
			if ($param['end_time'])    {$end_time=$param['end_time']+86400; $postWhere .= (" AND commentendtime<=  ".$end_time." ");}
			if ($param['seriesId']>-1) $postWhere .= " AND seriesid = {$param['seriesId']} ";
		
            return self::getConnObj()->select(
                "SELECT a.taskid,a.difficulty,a.maxsubmitnumber,a.srctid,a.commentendtime,a.submittime,a.status,b.pid, b.tid,b.`subject`,b.message,b.author,b.dateline".
				" FROM pre_ct_task a, pre_forum_post b  ".
				" WHERE a.srctid=b.tid AND a.`status`= :status ".
				$postWhere.
				" order by b.dateline desc limit {$param['start']},{$param['end']} ",
                [
                    'status' => $param['status'],
                ]
            );
			
			/*liu chuncheng
            return self::getConnObj()->select(
                "SELECT a.taskid,a.difficulty,a.maxsubmitnumber,a.srctid,a.commentendtime,a.submittime,a.status,b.pid, b.tid,b.`subject`,b.message,b.author,b.dateline FROM pre_ct_task a INNER join pre_forum_post b ON 
             a.srctid=b.tid  WHERE a.`status`= :status  and commentendtime BETWEEN {$param['start_time']} and {$param['end_time']} GROUP BY a.srctid order by b.dateline desc limit {$param['start']},{$param['end']} ",
                [
                    'status' => $param['status'],
                ]
            );
			*/
        }
        else
        {
            return self::getConnObj()->select(
                "SELECT a.taskid,a.difficulty,a.maxsubmitnumber,a.srctid,a.commentendtime,a.submittime,a.status,b.pid, b.tid,b.`subject`,b.message,b.author,b.dateline FROM pre_ct_task a INNER join pre_forum_post b ON 
             a.srctid=b.tid  WHERE a.`status`= :status and b.first=1 order by b.dateline desc limit {$param['start']},{$param['end']} ",
                [
                    'status' => $param['status'],
                ]
            );
        }



    }
    #获取最新已完成的任务
    public static function getMostRecentCompletedTask()
    {
		$maxcommentendday = self::getConnObj()->select("select max(round(commentendtime/86400)) maxcommentendday from pre_ct_task where status=3");
		if (!empty($maxcommentendday)){
			return self::getConnObj()->select("select * from pre_ct_task ct_task, pre_forum_thread ft".
				" where ct_task.srctid=ft.tid and round(commentendtime/86400)=".$maxcommentendday[0]->{'maxcommentendday'}." and ct_task.status=3 order by commentendtime desc");
		}
		else
			return array();
    }
	
    #获取某个状态的所有任务所属系列
    public static function getSeriesList($status)
    {
		return self::getConnObj()->select(
			"select ct_task.seriesId, seriesName from pre_ct_task ct_task, pre_ct_series ct_series where ct_task.seriesid=ct_series.seriesid and ct_task.status=$status group by ct_task.seriesid order by ct_task.seriesid"
		);	
    }

	public static function get_info_by_polishedbesttid($tid)
	{
		//tid: 译文对应论坛主题帖的tid
		return self::getConnObj()->select(
			"SELECT * from pre_ct_task where polishedbesttid=".$tid
		);
	}	
	
	public static function getSingleInfoByKV($key, $value)
	{
		//tid: 译文对应论坛主题帖的tid
		$r = self::getConnObj()->select(
			"SELECT * from pre_ct_task where ".$key."=".$value
		);
		if(!empty($r))
			return $r[0];
	}	
	
}